﻿--Populate Recipient Data:

insert into EcommPersonLoc 
select Current_Students.personid,enrollment.Housing as Location from current_students INNER JOIN Enrollment on Current_Students.PersonID = enrollment.personid
where enrollment_term=2 and enrollment_year = '2014-2015'

insert into EcommPersonLoc 
select persons.personid,'ToEnter' as Location from persons inner join enrollment on persons.personid = enrollment.personid
WHERE enrollment.Enrollment_Year = '2014-2015' AND Enrollment.Enrollment_Term=2 AND Enrollment.Enrollment_Type='Staff'

-- Populate Old Valentines Data --------------------------------
-- StudentList
insert into persons(Lname,Fname,Pname,Email,RVAID,Location,CanReceive,IsStudent)
SELECT cs.Lname, cs.Fname, Pname, Email, cs.PersonID AS RVAID, e.Housing as Location, 1 as CanReceive, 1 as IsStudent 
FROM ([MBOA].[CoreData].[dbo].[current_students] cs 
	INNER JOIN [MBOA].[CoreData].[dbo].[Enrollment] e on cs.PersonID = e.personid)
	INNER JOIN [MBOA].[CoreData].[dbo].[current_student_email] cse ON cs.PersonID = cse.PersonID 
where enrollment_term=2 and enrollment_year = '2014-2015'

-- StaffList
insert into persons(Lname,Fname,Pname,Email,RVAID,Location,CanReceive,IsStudent)
SELECT p.Lname, p.Fname, Pname, Email, p.PersonID AS RVAID, 'ToEnter' as Location, 1 as CanReceive, 0 as IsStudent 
from ([MBOA].[CoreData].[dbo].persons p
	inner join [MBOA].[CoreData].[dbo].enrollment e on p.personid = e.personid)
	inner join [MBOA].[CoreData].[dbo].[Email_Addresses] ea on p.PersonID = ea.PersonID
WHERE e.Enrollment_Year = '2014-2015' AND e.Enrollment_Term=2 AND e.Enrollment_Type='Staff'
 and Email like '%@rva.org'

-- ParentList
insert into persons(Lname,Fname,Pname,Email,RVAID,Location,CanReceive,IsStudent)
select cp.Lname, cp.Fname, Pname, Email, cp.PersonID AS RVAID,  null as Location, 0 as CanReceive, 0 as IsStudent 
From [MBOA].[CoreData].[dbo].[current_parents] cp 
	inner join (select min(email) email,personid from [MBOA].[CoreData].[dbo].Email_Addresses group by personid) em 
	on cp.PersonID = em.personid
Where not cp.personid in (select rvaid from Persons)

-- Load data from the new database back to the old one.
UPDATE m
SET [Logon] = 0
--select *
FROM openquery(Studdata97,'select * from [parent e-mail]') as m
INNER JOIN mboa.rafikiauth.dbo.aspnetusers ON [e-mail] = username
--WHERE [logon] = 0

-- final query - update the "logon" box in the student database based on accounts created in aspnetusers
UPDATE m
SET [Logon] = 1
--select * 
FROM openquery(Studdata97,'select * from [parent e-mail]') as m
INNER JOIN mboa.coredata.dbo.security_users_testing as sut ON EntityID -10000 = ParentID AND sut.logon = [e-mail]

select * FROM mboa.rafikiauth.dbo.aspnetusers
WHERE Username NOT IN (select [e-mail] from openquery(Studdata97,'select * from [parent e-mail] WHERE Logon = -1'))
AND Username like '%@%'

-- Move staff logins back to the old database
INSERT INTO OPENQUERY(STUDDATA97,'SELECT * FROM [Staff Logins]')(ParentID,Login)
select OldRVAID,Logon from mboa.coredata.dbo.persons
WHERE NOT Logon is null AND NOT Account_Number IS NULL and NOT PersonID-10000 IN 
(SELECT ParentID FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Staff Logins]'))